import datetime
import numpy as np
import matplotlib.colors as colors
import matplotlib.finance as finance
import matplotlib.dates as mdates
import matplotlib.ticker as mticker
import matplotlib.mlab as mlab
import matplotlib.pyplot as plt
import matplotlib.font_manager as font_manager
import pyodbc, datetime
from numpy import array
import matplotlib.dates as mdates
from matplotlib.ticker import FixedLocator
from matplotlib.dates import MinuteLocator, DateFormatter
from pylab import legend
from time import sleep

#startdate = datetime.date(2010,8,10)

def get_historical_database(ticker, startdate, enddate):
    #cnxn = pyodbc.connect('DSN=Stock')
    cnxn = pyodbc.connect('DSN=Stock;UID=bbc;PWD=cracker!1;')
    cursor = cnxn.cursor()
    query = "select [Time], [Open], [High], [Low], [Close], [Volume], [WAP]\
            from Stock.dbo.esh1_onemin\
            where Symbol = '" + ticker + "' and [Time] between '" + str(startdate) + "' and '" + str(enddate) + "'\
            order by [Time]"
    cursor.execute(query)
    result = cursor.fetchall()
    #_close = []
    #for r in result:
        #print r
    cnxn.close()
    return result

def update_fifo_queue(xlist, nvalue, length=50):
    if len(xlist) < length:
        xlist.append(nvalue)
    else:
        xlist.pop(0)

if __name__ == '__main__':
    startdate = datetime.date(2010,12,8)
    today = enddate = datetime.date(2011,03,18)
    ticker = 'ESH1'
    """
    key threshold
    """

    cnxn = pyodbc.connect('DSN=Stock')
    #cnxn = pyodbc.connect('DSN=Stock;UID=bbc;PWD=cracker!1;')
    cursor = cnxn.cursor()
    query1 = "SELECT TOP 1395 CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([High]-[Low]) As [Range]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND DATENAME(DW, [Time]) IN ('Sunday', 'Monday')\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query1)
    result = cursor.fetchall()
    #_datetime = [r[0] for r in result]
    _datetime = [mdates.date2num(datetime.datetime.strptime(r[0], '%H:%M:%S')) for r in result]
    # this is empirical ratio, maximum onemin value is about 26000
    _range0 = [float(r[1]) for r in result]

    query2 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([Volume]) As [Volume]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND DATENAME(DW, [Time]) = 'Sunday'\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query2)
    result = cursor.fetchall()

    #_datetime0 = [mdates.date2num(datetime.datetime.strptime(r[0], '%H:%M:%S')) for r in result]
    _volume0 = [float(r[1])/15000 for r in result]

    query1 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([High]-[Low]) As [Range]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND (DATENAME(DW, [Time]) = 'Sunday' OR (DATENAME(DW, [Time]) = 'Monday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query1)
    result = cursor.fetchall()
    #_datetime1 = [mdates.date2num(datetime.datetime.strptime(r[0], '%H:%M:%S')) for r in result]
    # this is empirical ratio, maximum onemin value is about 26000
    _range1 = [float(r[1]) for r in result]

    query2 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([Volume]) As [Volume]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND (DATENAME(DW, [Time]) = 'Sunday' OR (DATENAME(DW, [Time]) = 'Monday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query2)
    result = cursor.fetchall()

    #_datetime = [mdates.date2num(datetime.datetime.strptime(r[0], '%H:%M:%S')) for r in result]
    _volume1 = [float(r[1])/15000 for r in result]

    query1 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([High]-[Low]) As [Range]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND ((DATENAME(DW, [Time]) = 'Monday' AND CONVERT(VARCHAR(10), [Time], 8) > '17:30:00') OR (DATENAME(DW, [Time]) = 'Tuesday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query1)
    result = cursor.fetchall()
    _range2 = [float(r[1]) for r in result]

    query2 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([Volume]) As [Volume]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND ((DATENAME(DW, [Time]) = 'Monday' AND CONVERT(VARCHAR(10), [Time], 8) > '17:30:00') OR (DATENAME(DW, [Time]) = 'Tuesday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query2)
    result = cursor.fetchall()
    _volume2 = [float(r[1])/15000 for r in result]


    query1 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([High]-[Low]) As [Range]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND ((DATENAME(DW, [Time]) = 'Tuesday' AND CONVERT(VARCHAR(10), [Time], 8) > '17:30:00') OR (DATENAME(DW, [Time]) = 'Wednesday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query1)
    result = cursor.fetchall()
    _range3 = [float(r[1]) for r in result]

    query2 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([Volume]) As [Volume]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND ((DATENAME(DW, [Time]) = 'Tuesday' AND CONVERT(VARCHAR(10), [Time], 8) > '17:30:00') OR (DATENAME(DW, [Time]) = 'Wednesday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query2)
    result = cursor.fetchall()
    _volume3 = [float(r[1])/15000 for r in result]


    query1 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([High]-[Low]) As [Range]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND ((DATENAME(DW, [Time]) = 'Wednesday' AND CONVERT(VARCHAR(10), [Time], 8) > '17:30:00') OR (DATENAME(DW, [Time]) = 'Thursday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query1)
    result = cursor.fetchall()
    _range4 = [float(r[1]) for r in result]

    query2 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([Volume]) As [Volume]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND ((DATENAME(DW, [Time]) = 'Wednesday' AND CONVERT(VARCHAR(10), [Time], 8) > '17:30:00') OR (DATENAME(DW, [Time]) = 'Thursday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query2)
    result = cursor.fetchall()
    _volume4 = [float(r[1])/15000 for r in result]

    query1 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([High]-[Low]) As [Range]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND ((DATENAME(DW, [Time]) = 'Thursday' AND CONVERT(VARCHAR(10), [Time], 8) > '17:30:00') OR (DATENAME(DW, [Time]) = 'Friday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query1)
    result = cursor.fetchall()
    _datetime_fri = [mdates.date2num(datetime.datetime.strptime(r[0], '%H:%M:%S')) for r in result]
    _range5 = [float(r[1]) for r in result]

    query2 = "SELECT CONVERT(VARCHAR(10),[Time],8) AS [Time], AVG([Volume]) As [Volume]\
            FROM Stock.dbo.esh1_onemin\
            WHERE Symbol = 'ESH1'\
            AND ((DATENAME(DW, [Time]) = 'Thursday' AND CONVERT(VARCHAR(10), [Time], 8) > '17:30:00') OR (DATENAME(DW, [Time]) = 'Friday' AND CONVERT(VARCHAR(10), [Time], 8) < '17:30:00'))\
            GROUP BY CONVERT(VARCHAR(10), [Time], 8)\
            ORDER BY CONVERT(VARCHAR(10), [Time], 8) ASC"
    cursor.execute(query2)
    result = cursor.fetchall()
    _volume5 = [float(r[1])/15000 for r in result]

    fig = plt.figure()
    plt.title('Intraday One Minute Range/Volume of ESH1')
    #plt.rc('grid', color='0.75', linestyle='-', linewidth=0.5)

    left, width = 0.05, 0.9
    rect0 = [left, 0.05, width, 0.15]
    rect1 = [left, 0.8, width, 0.15]
    rect2 = [left, 0.65, width, 0.15]
    rect3 = [left, 0.5, width, 0.15]
    rect4 = [left, 0.35, width, 0.15]
    rect5 = [left, 0.2, width, 0.15]
    ax0 = fig.add_axes(rect0)
    ax1 = fig.add_axes(rect1)
    ax2 = fig.add_axes(rect2)
    ax3 = fig.add_axes(rect3)
    ax4 = fig.add_axes(rect4)
    ax5 = fig.add_axes(rect5)
    #ax = fig.add_subplot(2,1,1)
    #ax.fmt_xdata = mdates.DateFormatter('%H:%M:%S')
    #ax.set_xlabel(str(_datetime))
    #ax.plot(datetime_t, range_t, 'k')
    desc = '-'
    l01 = ax0.plot(_datetime, _range0, desc, color='r')
    l02 = ax0.plot(_datetime, _volume0, desc, color='g')
    #ax.plot(_range, '-')

    #legend((l1, l2), ('volume 15,000', 'range 1pt'), 'upper right', shadow=True)
    #ax = fig.add_subplot(2,1,2)

    l11 = ax1.plot(_datetime, _range1, desc, label='Sunday', color='r')
    l12 = ax1.plot(_datetime, _volume1, desc, color='g')
    l21 = ax2.plot(_datetime, _range2, desc, label='Monday', color='r')
    l22 = ax2.plot(_datetime, _volume2, desc, color='g')
    l31 = ax3.plot(_datetime, _range3, desc, label='Tuesday', color='r')
    l32 = ax3.plot(_datetime, _volume3, desc, color='g')
    l41 = ax4.plot(_datetime, _range4, desc, label='Wednesday', color='r')
    l42 = ax4.plot(_datetime, _volume4, desc, color='g')
    l51 = ax5.plot(_datetime_fri, _range5, desc, label='Thursday', color='r')
    l52 = ax5.plot(_datetime_fri, _volume5, desc, color='g')

    intraday = MinuteLocator(range(-1,1441), interval = 60)
    intradayFmt = DateFormatter('%H:%M:%S')
    yrange = FixedLocator(np.arange(0, 2.5, 0.5))

    for ax in ax0, ax1, ax2, ax3, ax4, ax5:
        if ax != ax0:
            for label in ax.get_xticklabels():
                label.set_visible(False)
        else:
            for label in ax.get_xticklabels():
                label.set_rotation(30)
                label.set_horizontalalignment('right')

        ax.xaxis.set_major_locator(intraday)
        ax.xaxis.set_major_formatter(intradayFmt)
        ax.yaxis.set_major_locator(yrange)

    props = font_manager.FontProperties(size=10)
    leg0 = ax0.legend((l01, l02), ('Volume 15,000', 'Range 1pt'), loc='upper right', shadow=True, fancybox=True, prop=props)

    ax0.text(0.5, 0.5, 'All', horizontalalignment='center', verticalalignment='center', transform=ax0.transAxes, fontsize=20)
    ax1.text(0.5, 0.5, 'Sunday', horizontalalignment='center', verticalalignment='center', transform=ax1.transAxes, fontsize=20)
    ax2.text(0.5, 0.5, 'Monday', horizontalalignment='center', verticalalignment='center', transform=ax2.transAxes, fontsize=20)
    ax3.text(0.5, 0.5, 'Tuesday', horizontalalignment='center', verticalalignment='center', transform=ax3.transAxes, fontsize=20)
    ax4.text(0.5, 0.5, 'Wednesday', horizontalalignment='center', verticalalignment='center', transform=ax4.transAxes, fontsize=20)
    ax5.text(0.5, 0.5, 'Thursday', horizontalalignment='center', verticalalignment='center', transform=ax5.transAxes, fontsize=20)

    ax0.set_title('Intraday One Minute Range/Volume of ESH1')
    #plt.xlabel('intraday time')
    #plt.ylabel('range/volume of onemin bar')
    ax0.autoscale_view()
    ax1.autoscale_view()
    ax0.grid(True)
    #ax1.grid(True)
    #ax2.grid(True)
    #ax3.grid(True)
    #ax4.grid(True)
    #ax5.grid(True)

    #xticklabels = ax0.get_xticklabels()+ax1.get_xticklabels()
    #plt.setp(xticklabels, visible=False)

    #fig.autofmt_xdate()
    plt.show()
    #print _datetime
    #print _range
